package com.liuxl.db;

import java.util.List;

import com.google.common.collect.Lists;
import com.liuxl.db.bo.SQL;

/**
 * SQL语句工具类
 * Created by LiuXiaoLong at 2021年1月12日
 */
public class SQLUtil {
	
	private static final String PARAM_PREFIX = "#{";//SQL参数前缀
	private static final String PARAM_SUFFIX = "}";//SQL参数后缀
	private static final String SQL_PLACEHOLDER = "?";//JDBC 统一SQL占位符标识
	
	/**
	 * 解析SQL参数并返回参数名集合
	 * @param sql
	 * @return
	 */
	public static List<String> getSqlParam(String sql){
		List<String> results = Lists.newArrayList();
		StringBuffer sb = new StringBuffer(sql);
		int start = sb.indexOf(PARAM_PREFIX);
		if (start == -1) {
			return results;
		}
		while (start != -1) {
			int end = sb.indexOf(PARAM_SUFFIX, start);
			if(end != -1) {
				String str = sb.substring(start+PARAM_PREFIX.length(), end);
				results.add(str.trim());
				start = sb.indexOf(PARAM_PREFIX,end);
			}else {
				start = sb.indexOf(PARAM_PREFIX, start+PARAM_PREFIX.length());
			}
		}
		
		
		return results;
	}
	

	/**
	 * 替换SQL参数
	 * 		将SQL#{xxx}占位符传参替换为JDBC统一占位符（?）
	 * @param sql
	 * @return
	 */
	public static String replaceSqlParam(String sql) {
		
		StringBuffer sb = new StringBuffer(sql);
		int start = sb.indexOf(PARAM_PREFIX);
		if (start == -1) {
			return sql;
		}
		while (start != -1) {
			int end = sb.indexOf(PARAM_SUFFIX, start);
			if (end != -1) {//若有后缀则从前缀到后缀位置之间进行替换
				sb.replace(start, end+PARAM_SUFFIX.length(),SQL_PLACEHOLDER);
				start = sb.indexOf(PARAM_PREFIX);//从当前后缀位置开始查找下一个前缀
			}else {
				start = sb.indexOf(PARAM_PREFIX, start+PARAM_PREFIX.length());//若后缀未命中，则跳过，继续查找
			}
		}
		return sb.toString();
	}
	
	//select name,id from (select * from t_test) t1  where  id in (#{id0},#{id1} )
	/**
	 * 解析SQL（替换参数占位符和获取参数）
	 * @param sql
	 * @return
	 */
	public static SQL resolveSql(String sql) {
		SQL _sql = new SQL();
		StringBuffer sb = new StringBuffer(sql);
		int start = sb.indexOf(PARAM_PREFIX);
		List<String> parameterNames = Lists.newArrayList();
		while (start != -1) {
			int end = sb.indexOf(PARAM_SUFFIX, start);
			if (end != -1) {//若有后缀则从前缀到后缀位置之间进行替换
				String str = sb.substring(start+PARAM_PREFIX.length(), end);
				parameterNames.add(str.trim());
				sb.replace(start, end+PARAM_SUFFIX.length(),SQL_PLACEHOLDER);
//				int len = end+PARAM_SUFFIX.length() - start;
//				start = sb.indexOf(PARAM_PREFIX, end);//从当前后缀位置开始查找下一个前缀
				start = sb.indexOf(PARAM_PREFIX);
			}else {
				start = sb.indexOf(PARAM_PREFIX, start+PARAM_PREFIX.length());//若后缀未命中，则跳过，继续查找
			}
		}
		_sql.setSelectSql(sb.toString());
		_sql.setParameterNames(parameterNames);
		return _sql;
	}
	
	public static void main(String[] args) {
		SQL sql = resolveSql("select name,id from (select * from t_test) t1  where  id = #{id},name=#{name} )");
		System.out.println(sql.getSelectSql());
		System.out.println("select name,id from (select * from t_test) t1  where  id in (#{id0},#{id1} )".lastIndexOf(PARAM_PREFIX));
	}
	
}
